/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DBlayer;
import Modellayer.*;
import java.sql.*;
import java.util.ArrayList;
/**
 *
 * @author Engel
 */
public class DBCity implements IFDBCity
{

	private Connection con;

	public DBCity()
	{
	con = DBConnection.getInstance().getDBcon();
	}

	public ArrayList<City> getAllCity()
    {
        return miscWhere("");
    }

	public City findCity(int odeZipC)
    {
        String wClause = "  odeZipC = '" + odeZipC + "'";
        return singleWhere(wClause);
    }


	public int insertCity(City city)
	{
	int rc = -1;
    String query="INSERT INTO persiCity(City, OdeZipC)  VALUES('"+
	  		city.getCity()  + "','"  +
			city.getOdeZipC();

      System.out.println("insert : " + query);
      try{ // insert new city
            Statement stmt = con.createStatement();
	 	  	stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
		}//end try
	 	catch(Exception ex){
	 	  	System.out.println("Insert exception in persiCity db: "+ex);
	   }
	   return(rc);
	}

	public int updateCity(City city)
	{
		City cityObj  = city;
		int rc=-1;

		String query="UPDATE persiCity SET "+
                        "city ='" + cityObj.getCity() + "', " +
		    	"OdeZipC ='" + cityObj.getOdeZipC();

                System.out.println("Update query:" + query);
  		try{ // update city
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//slut try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in customer db: "+ex);
	  	}
		return(rc);
    }

	public void delete(int odeZipC)
	{
               int rc=-1;

	  	String query="DELETE FROM persiCity WHERE odeZipC = '" +
				odeZipC + "'";
                System.out.println(query);
	  	try{ // delete from city
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in persiCity db: "+ex);
   	        }
	}

	private ArrayList<City> miscWhere(String wClause)
	{
            ResultSet results;
	    ArrayList<City> list = new ArrayList<City>();

	    String query =  buildQuery(wClause);

            try{ // read the persiCity from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);

		while( results.next() ){
	     	 City cityObj = new City();
		 cityObj = buildCity(results);
                 list.add(cityObj);
		}//end while
                 stmt.close();

		}//slut try
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}

	//Singelwhere is used when we only select one city
	private City singleWhere(String wClause)
	{
		ResultSet results;
		City cityObj = new City();

	        String query =  buildQuery(wClause);
                System.out.println(query);
		try{ // read the persiCity from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);

	 		if( results.next() ){
                            cityObj = buildCity(results);
                            //assocaition is to be build
                            stmt.close();
			}
                        else{ //no employee was found
                            cityObj = null;
                        }
		}//end try
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return cityObj;
	}
	//method to build the query
	private String buildQuery(String wClause)
	{
	    String query="SELECT city, odeZipC FROM persiCity";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;

		return query;
	}

	private City buildCity(ResultSet results)
      {   City cityObj = new City();
          try{ // the columns from the table persiCity  are used
                cityObj.setCity(results.getString("city"));
                cityObj.setOdeZipC(results.getInt("odeZipC"));
          }
         catch(Exception e)
         {
             System.out.println("error in building the city object");
         }
         return cityObj;
      }
}
